const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 Analyzing Database Structure Inconsistency');
console.log('=============================================');

// Check instance_field_mappings table
try {
  const mappingExists = db.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name = 'instance_field_mappings'`).get();
  console.log('Instance field mappings table exists:', Boolean(mappingExists));

  if (mappingExists) {
    const mappingCount = db.prepare('SELECT COUNT(*) as count FROM instance_field_mappings').get();
    console.log('Field mappings count:', mappingCount.count);
  }
} catch (error) {
  console.log('Error checking mappings table:', error.message);
}

console.log('\n📊 Verification: Check what structure is actually used');

// Check all device instance tables
const allInstanceTables = db.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'device_instances_%'`).all();
console.log('Total device instance tables:', allInstanceTables.length);

// Check a sample table structure
const sampleTable = 'device_instances_piping_cooling_water_spray_system';
const tableInfo = db.prepare(`PRAGMA table_info(${sampleTable})`).all();

console.log(`\nSample table: ${sampleTable}`);
console.log('Columns:');

const hasNamingRecordId = tableInfo.some(col => col.name === 'naming_record_id');
const hasNamingFields = tableInfo.some(col => col.name.startsWith('naming_') && col.name !== 'naming_record_id');

console.log('Has naming_record_id:', hasNamingRecordId);
console.log('Has naming_ fields:', hasNamingFields);

if (hasNamingFields) {
  console.log('\nNaming fields found:');
  tableInfo.filter(col => col.name.startsWith('naming_')).forEach(col => {
    console.log(`  - ${col.name}`);
  });
}

console.log('\n🎯 CONCLUSION:');
if (hasNamingFields && !hasNamingRecordId) {
  console.log('✅ System uses ENHANCED structure (embedded fields)');
  console.log('❌ Code looking for naming_record_id is INCORRECT');
} else if (hasNamingRecordId && !hasNamingFields) {
  console.log('✅ System uses BASIC structure (record IDs)');
} else {
  console.log('⚠️  Mixed or unclear structure detected');
}

db.close();